﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ProdeBackEnd.Model;
using ProdeBackEnd.Libs;
using System.Data;

namespace ProdeBackEnd.DAO
{
    public class TournamenteService
    {
        private static TournamenteService instance;
        private static String TBTournament = "Tournament";
        private static String TBTeamTournament = "TeamTournament";

        private TournamenteService()
        {}

        public static TournamenteService getTournamenteServiceInstance()
        {
            if (instance == null)
            {
                instance = new TournamenteService();
            }
            return instance;
        }

        public Tournament getTournamentById(long id)
        {
            Tournament tournament = null;

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTournament + " " +
                            "WHERE ID = " + id;

            DataTable tournamentTable = conn.executeQuery(SQL);

            foreach (DataRow tournamentRow in tournamentTable.Rows)
            {
                tournament = getTournament(tournamentRow);
                tournament.setTeams(getTeamTournament(id));
            }

            if (tournament == null)
            {
                throw new BusinessException("301", "No se encontro el elemento solicidado");
            }
            return tournament;
        }

        public List<Tournament> getTournamentListFilter(String filter)
        {
            List<Tournament> tournamentList = new List<Tournament>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTournament + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable tournamentTable = conn.executeQuery(SQL);

            foreach (DataRow tournamentRow in tournamentTable.Rows)
            {
                Tournament tournament = getTournament(tournamentRow);
                tournamentList.Add(tournament);
            }
            return tournamentList;
        }

        public Tournament save(Tournament tournament)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            if (tournament.getId() != 0) //Si es diferente de 0 es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBTournament + " SET " +
                      "Name = '" + tournament.getName() + "', " +
                      "Start = '" + tournament.getStart() + "', " +
                      "Finish = '" + tournament.getEnd() + "', " +
                      "WHERE id = " + tournament.getId() + " ";

                if (tournament.getTeams() != null)
                {
                    foreach (Team team in tournament.getTeams())
                    {
                        saveTeamTournament(tournament, team);
                    }
                }
            }
            else //Si es null es por que es un objeto nuevo
            {
                tournament.setId(getNewId());
                SQL = "INSERT INTO " + TBTournament + " " +
                       "(id, Name, Start, finsh) VALUES (" +
                       "" + tournament.getId() + ", " +
                       "'" + tournament.getName() + "', " +
                       "'" + tournament.getStart() + "', " +
                       "'" + tournament.getEnd() + "') ";
            }

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return tournament;
        }

        public void delete(long id)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            SQL = "DELETE  " + TBTournament + "  " +
                    "WHERE id = " + id + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("303", "No se pudo borrar el elemento");
            }
        }

        public void saveTeamTournament(Tournament tournamente, Team team)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            SQL = "INSERT INTO " + TBTeamTournament + " " +
                    "(idTeam, idTournament) VALUES (" +
                    "" + team.getId() + ", " +
                    "" + tournamente.getId() + ") ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));

            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }
        }

        public List<Team> getTeamTournament(long idTournament)
        {
            List<Team> teamList = new List<Team>();
            TeamService teamService = TeamService.getTeamServiceInstance();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeamTournament + " " +
                         "WHERE idTournament = " + idTournament + " ";

            DataTable tournamentTable = conn.executeQuery(SQL);

            foreach (DataRow tournamentRow in tournamentTable.Rows)
            {
                long idTeam = Convert.ToInt64(tournamentRow["idTeam"].ToString());
                Team team = teamService.getTeamById(idTeam);
                teamList.Add(team);
            }
            return teamList;
        }

        private Tournament getTournament(DataRow tournamentRow)
        {
            Tournament tournament = new Tournament(Convert.ToInt64(tournamentRow["id"].ToString()),
                                                    tournamentRow["name"].ToString(),
                                                    Convert.ToDateTime(tournamentRow["Start"].ToString()),
                                                    Convert.ToDateTime(tournamentRow["Finish"].ToString()));
            return tournament;
        }

        private long getNewId()
        {
            long id = 0L;
            String strId;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT MAX(id) FROM " + TBTournament + " ";

            DataTable tournamentTable = conn.executeQuery(SQL);

            foreach (DataRow tournamentRow in tournamentTable.Rows)
            {
                strId = tournamentRow[0].ToString();
                if (strId.Equals(""))
                {
                    strId = "0";
                }
                id = Convert.ToInt64(strId) + 1;
            }
            return id;
        }
    }
}